import datetime
import pymysql
import requests

from dbutils.pooled_db import PooledDB


# 测试库
def getBetaConnection():
    pool = PooledDB(pymysql, 1, host='172.20.150.110', user='test_movie', passwd='movie20189933',
                db='cc_media_source', port=4308)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 正式库
def getProdConnection():
    pool = PooledDB(pymysql, 1, host='193.112.206.244', user='developer_read', passwd='developer_read#coocaa',
                    db='cc_media_source', port=3306)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


#删除背景图为空的数据
def delRepeatPoetry():
    conn, cur = getProdConnection()
    try:
        batch_size = 100
        offset = 0

        while True:
            # 使用LIMIT和OFFSET进行分页查询
            select_sql = f"""
                select id from cc_ancient_poetry_image t  where t.background_image = ''
                LIMIT {batch_size} OFFSET {offset}
                """
            cur.execute(select_sql)
            results = cur.fetchall()

            if not results:
                # 如果没有更多数据，则退出循环
                break
            ids = []
            for result in results:
                id =result[0]
                ids.append(id)
                #print(f"id:{id}")
            deleteApi(ids)
            # 更新offset以进行下一次查询
            offset += batch_size
            print(f"第{offset}序号的数据进行删除")
            # 删除这批数据到API



    finally:
        # 确保无论如何都关闭数据库连接
        cur.close()
        conn.close()

#删除指定的范围的古诗文背景图
def deleteByNumber():
    # 定义起始和结束ID
    start_id = 69370
    end_id = 104069

    # 定义每批删除的ID数量
    batch_size = 100

    # 生成ID列表
    id_list = list(range(start_id, end_id + 1))

    # 分割ID列表为多个批次
    batches = [id_list[i:i + batch_size] for i in range(0, len(id_list), batch_size)]

    # 对每一批ID调用删除函数
    for ids in batches:
        deleteApi(ids)


def deleteApi(ids):
    # 定义删除接口的URL（不含查询参数）
    delete_url = "http://106.55.8.90:7001/api/ancientPoetry/deletePoetryImage/invoke"
    #delete_url = "http://localhost:8080/api/ancientPoetry/deletePoetryImage/invoke"
    # 构造查询参数
    params = {'ids': ','.join(map(str, ids))}
    # 发送HTTP GET请求
    response = requests.get(delete_url, params=params)
    # 检查响应状态码
    if response.status_code == 200:
        print(f"Successfully deleted IDs,删除数量:{len(ids)}")
    else:
        print(f"Failed to delete IDs: {','.join(map(str, ids))}, Status Code: {response.status_code}, Response: {response.text}")

if __name__ == '__main__':
    #deleteByNumber()
    delRepeatPoetry()